package com.ningxia.water.mapper;

import com.ningxia.water.entity.TsApplicantPlan;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.ningxia.water.vo.out.ChartVo;
import com.ningxia.water.vo.out.PlanVolumeVo;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 *
 * @author hz
 * @since 2021-03-15
 */
public interface TsApplicantPlanMapper extends BaseMapper<TsApplicantPlan> {


    @Select("SELECT sum(plan_volume)/10000 as plan FROM ts_applicant_plan where plan_year = #{year}")
    Double getPlanVolume(String year);

    /**
     * 配额管理中 获取用水户id、用水户名称、第一批用水计划量、第二批用水计划量、总用水计划量、许可用水量、实际用水量
     * @param applicantName 用水户名称
     * @param currentYear   年份
     * @return
     */
    @Select("<script>" +
            "select " +
            "ai.applicant_id, " +
            "ai.applicant_name, " +
            "ap1.plan_volume/10000 as firstPlanVolume, " +
            "ap2.plan_volume/10000 as secondPlanVolume," +
            "(IFNULL(ap1.plan_volume,0)+IFNULL(ap2.plan_volume,0))/10000 as totalPlanVolume, " +
            "sum(li.license_volume/10000) as licenseVolume, " +
            "used.total/10000 as totalUsedVolume\n" +
            "from ts_applicant_info ai\n" +
            "LEFT JOIN " +
            "ts_applicant_plan ap1 " +
            "on " +
            "ai.applicant_id = ap1.applicant_id and ap1.plan_type = 1 and ap1.plan_year = #{currentYear}\n" +
            "LEFT JOIN " +
            "ts_applicant_plan ap2 " +
            "on " +
            "ai.applicant_id = ap2.applicant_id and ap2.plan_type = 2 and ap2.plan_year = #{currentYear}\n" +
            "LEFT JOIN " +
            "ts_license_info li " +
            "on " +
            "ai.applicant_id = li.applicant_id and li.available  = 1 \n" +
            "LEFT JOIN (select ai.applicant_id, ai.applicant_name, sum(md.month_data) as total\n" +
            "          from \n" +
            "            ts_applicant_info ai\n" +
            "            LEFT JOIN ts_applicant_well aw on ai.applicant_id = aw.applicant_id\n" +
            "            LEFT JOIN ts_month_data md on md.well_id = aw.well_id\n" +
            "            where md.time BETWEEN concat(concat(#{currentYear},'-01')) and concat(#{currentYear},'-12')\n" +
            "            GROUP BY ai.applicant_id) used " +
            "on ai.applicant_id = used.applicant_id" +
            "<where>" +
            "<if test='applicantName!=null'>" +
            "and ai.applicant_name like concat('%', #{applicantName}, '%')  " +
            "</if>" +
            "</where>" +
            "group by ai.applicant_id " +
            "</script>")
    List<PlanVolumeVo> getPlanVolumeInfos(String applicantName, String currentYear);

    /**
     * 根据年份获取当年用水计划，返回字段：用水户名称、第一批用水计划、第二批用水计划、总用水计划
     * @param applicantId   用水户id
     * @param planYear      计划年份
     * @return
     */
    @Select("SELECT " +
            "ai.applicant_name, " +
            "ap1.plan_volume/10000 as firstPlanVolume, " +
            "ap2.plan_volume/10000 as secondPlanVolume, " +
            "(IFNULL(ap1.plan_volume,0)+IFNULL(ap2.plan_volume,0))/10000 as totalPlanVolume\n" +
            "from ts_applicant_info ai\n" +
            "LEFT JOIN ts_applicant_plan ap1 on ai.applicant_id = ap1.applicant_id and ap1.plan_type = 1 and ap1.plan_year = #{planYear}\n" +
            "LEFT JOIN ts_applicant_plan ap2 on ai.applicant_id = ap2.applicant_id and ap2.plan_type = 2 and ap2.plan_year =  #{planYear}\n" +
            "where ai.applicant_id = #{applicantId}")
    PlanVolumeVo getPlanVolumeByYear(Integer applicantId, String planYear);

}
